package gestionBDD;

import java.util.*;

import java.sql.*;

import interne.*;

public class extractionBDD {
	
	/*
	 * Extrait tous les albums dont l'attribut est partage = vrai
	 */
	public static List<Album> extraireAlbumsPub() throws SQLException {
	    List<Album> listeAlbums = new ArrayList<Album>();
		try {
			String sql = "SELECT * FROM ALBUM where partage = 1";
			PreparedStatement statement = accesBDD.getInstance().prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                // Alimentation ArrayList listeAlbum
            Album album = new Album(rs.getInt("ID_ALBUM"), rs.getInt("ID_CLIENT"), rs.getString("TITRE_ALBUM"), 
            		rs.getString("SOUS_TITRE"), rs.getBoolean("PARTAGE"));
                listeAlbums.add(album);           
              }           
		}
		catch(SQLException e) {
			System.out.println(e) ;
		}
		return listeAlbums;
		
	}
	
	/*
	 * Extrait tous les albums d'un client
	 */
	public static List<Album> extraireAlbumsPriv(Client c) throws SQLException {
	    List<Album> listeAlbums = new ArrayList<Album>();
		try {
			String sql = "SELECT * FROM ALBUM where id_client = ? ";
			PreparedStatement statement = accesBDD.getInstance().prepareStatement(sql);
			statement.setObject(1, c.getIdClient());
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                // Alimentation ArrayList listeAlbum
                Album album = new Album(rs.getInt("ID_ALBUM"), rs.getInt("ID_CLIENT"), rs.getString("TITRE_ALBUM"), 
                		rs.getString("SOUS_TITRE"), rs.getBoolean("PARTAGE"));
                listeAlbums.add(album);           
              }           
		}
		catch(SQLException e) {
			System.out.println(e) ;
		}
		return listeAlbums;
	}
	
	/*
	 * Extrait à la fois les albums d'un client, et ceux partagés
	 */
	public static List<Album> extraireAlbumsCommandables(Client c) throws SQLException {
	    List<Album> listeAlbums = new ArrayList<Album>();
		try {
			String sql = "SELECT * FROM ALBUM where id_client = ? OR PARTAGE = 1";
			PreparedStatement statement = accesBDD.getInstance().prepareStatement(sql);
			statement.setObject(1, c.getIdClient());
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                // Alimentation ArrayList listeAlbum
                Album album = new Album(rs.getInt("ID_ALBUM"), rs.getInt("ID_CLIENT"), rs.getString("TITRE_ALBUM"), rs.getString("SOUS_TITRE"), rs.getBoolean("PARTAGE"));
                listeAlbums.add(album);           
              }           
		}
		catch(SQLException e) {
			System.out.println(e) ;
		}
		return listeAlbums;
	}
	
	/*
	 * Extrait l'ensemble des commandes passées par un client
	 */
	public static List<Commande> extraireCommandes(Client c) throws SQLException {
	    List<Commande> listeCommandes = new ArrayList<Commande>();
		try {
			String sql = "SELECT * FROM COMMANDE where id_client = ?";
			PreparedStatement statement = accesBDD.getInstance().prepareStatement(sql);
			statement.setObject(1, c.getIdClient());
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                // Alimentation ArrayList listeAlbum
            	Commande commande = new Commande(rs.getInt("REFERENCE"), rs.getInt("ID_CLIENT"), rs.getTimestamp("DATE"), 
            			rs.getFloat("MONTANT_TOTAL"), rs.getString("NOM_DEST"), rs.getString("PRENOM_DEST"), rs.getString("ADRESSE_DEST"));
            	listeCommandes.add(commande);           
              }           
		}
		catch(SQLException e) {
			System.out.println(e) ;
		}
		return listeCommandes;
	}
	
	/*
	 * Extrait toutes les photos contenues dans un album
	 */
	public static List<Contient> extrairePhotos(Album a) throws SQLException {
	    List<Contient> listePhotos = new ArrayList<Contient>();
		try {
			String sql = "SELECT * FROM CONTIENT WHERE id_album = ? ORDER BY ordre";
			PreparedStatement statement = accesBDD.getInstance().prepareStatement(sql);
			statement.setObject(1, a.getIdAlbum());
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                // Alimentation ArrayList listeAlbum
                Contient photo = new Contient(rs.getInt("ID_CONTIENT"), rs.getInt("ID_ALBUM"), rs.getInt("ID_IMAGE"), rs.getInt("ORDRE"), rs.getString("TITRE_IMAGE"), rs.getString("COMMENTAIRE"));
                listePhotos.add(photo);
              }           
		}
		catch(SQLException e) {
			System.out.println(e) ;
		}
		return listePhotos;
	}

	/*
	 * Extrait tous les formats de tirages existant
	 */
	public static List<Tirage> extraireTirages() throws SQLException {
	    List<Tirage> listeTirages = new ArrayList<Tirage>();
		try {
			String sql = "SELECT * FROM TIRAGE" ;
			PreparedStatement statement = accesBDD.getInstance().prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                // Alimentation ArrayList listeAlbum
                Tirage tirage = new Tirage(rs.getInt("ID_FORMAT"), rs.getString("LIBELLE"), rs.getFloat("PRIX_UNITAIRE"));
                listeTirages.add(tirage);
                System.out.println(rs.getString("LIBELLE"));
              }           
		}
		catch(SQLException e) {
			System.out.println(e) ;
		}
		return listeTirages;
	}	
	
	/*
	 * Extrait tous les produits commandées pour une commande
	 */
	public static List<LigneCommande> extraireLignesCommandes(Commande c) {
	    List<LigneCommande> listeLignesCommandes = new ArrayList<LigneCommande>();
		try {
			String sql = "SELECT * FROM LIGNE_COMMANDE where reference = ? " ;
			PreparedStatement statement = accesBDD.getInstance().prepareStatement(sql);
			statement.setObject(1, c.getReference());
            ResultSet rs = statement.executeQuery();
            while (rs.next()) {
                // Alimentation ArrayList listeAlbum
            	LigneCommande lc = new LigneCommande(rs.getInt("ID_LIGNE"), rs.getInt("REFERENCE"), rs.getInt("ID_FORMAT"), rs.getInt("ID_ALBUM"), rs.getInt("QUANTITE"), rs.getFloat("MONTANT"));
            	listeLignesCommandes.add(lc);           
              }           
		}
		catch(SQLException e) {
			System.out.println("extraireLignesCommandes() : " + e) ;
		}
		return listeLignesCommandes;		
	}
	
}
